#coding=utf8

# 表一：
# 表二：
# 工作簿排列顺序（之前表一和表二顺序不同）
# 到课率 晚归 早锻炼 卫生
# 格式要求：
#     表格1 第三行开始是学生姓名


from __future__ import unicode_literals
import json,os,re
import xlrd
from xlrd import open_workbook
from xlutils.copy import copy as xlscopy

name_table_1 = None
name_table_2 = None

try:
    for i in os.listdir('.'):
        if i.find('~')!=-1:continue
        if i.find("表一")!=-1:
            assert name_table_1==None
            name_table_1 = i
        elif i.find("表二")!=-1:
            assert name_table_2==None
            name_table_2 = i
        
    assert name_table_1!=None
    assert name_table_2!=None
except Exception as e:
    print "请确认当前目录有且只有两个分别含有 表一 表二 的表格"
    print e
    raw_input()
    exit()
print u'请输入周次：',
week = int(raw_input())


def xlstodict(filename):
    c = xlrd.open_workbook(filename)
    table = []
    for sheet in c.sheets():
        sheets = []
        for row in range(sheet.nrows):
            value = []
            for col in range(sheet.ncols):
                value.append(sheet.cell(row,col).value)
            sheets.append(value)
        table.append(sheets)
    return table


def pr(s):print(json.dumps(s,ensure_ascii=False,indent=0))
def trim(s):return s.replace(' ','')

table1 = xlstodict(name_table_1)
to = lambda x:dict([ [trim(i[0]),i[4]] for i in x[2:] ])
table1 = [to(i) for i in table1]

xd = xlstodict(name_table_2)
xr = open_workbook(name_table_2,formatting_info=True)
xw = xlscopy(xr)

def write(xdn,t2_n,xw_n,multiple=1):
    xwn = xw.get_sheet(xw_n)
    for i,v in enumerate(xdn):
        name =  trim(v[0])
        if t2_n.has_key(name):
            num = t2_n[name]*multiple
            xwn.write(i,week+1,num)
            del t2_n[name]
    if len(t2_n):
        print u"表格2中第%d个工作簿中没有下述学生的姓名，请在对应班级加上"%(xw_n+1)
        for k in t2_n:
            print '\t',k,t2_n[k]
        print '----------\n'

write(xd[0],table1[0],0,2)
write(xd[1],table1[1],1,1)
write(xd[2],table1[2],2,1)
write(xd[3],table1[3],3,1)

xw.save('表格2-生成.xls')

print("生成成功。 表格2-生成.xls")
